package com.ruoyi.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.ruoyi.entity.ExcelData;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

@Service
@Slf4j
public class ExcelAnalysisService {

    public Map<String, Object> analyzeExcel(MultipartFile file) throws IOException {
        // 存储解析结果
        List<Map<String, Object>> dataList = new ArrayList<>();

        // 读取Excel文件
        EasyExcel.read(file.getInputStream(), ExcelData.class, new AnalysisEventListener<ExcelData>() {
            @Override
            public void invoke(ExcelData data, AnalysisContext context) {
                // 将每行数据转换为Map
                Map<String, Object> rowMap = new HashMap<>();
                rowMap.put("column1", data.getColumn1());
                rowMap.put("column2", data.getColumn2());
                rowMap.put("column3", data.getColumn3());
                rowMap.put("column4", data.getColumn4());
                rowMap.put("column5", data.getColumn5());
                rowMap.put("column6", data.getColumn6());
                rowMap.put("column7", data.getColumn7());
                rowMap.put("column8", data.getColumn8());
                rowMap.put("column9", data.getColumn9());
                rowMap.put("column10", data.getColumn10());

                dataList.add(rowMap);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("Excel分析完成，共{}行", dataList.size());
            }
        }).sheet().doRead();

        // 构建分析结果
        return buildAnalysisResult(dataList);
    }

    private Map<String, Object> buildAnalysisResult(List<Map<String, Object>> dataList) {
        Map<String, Object> result = new HashMap<>();

        // 基本信息
        result.put("rows", dataList.size());

        if (dataList.isEmpty()) {
            return result;
        }

        // 获取列名
        Set<String> columnNames = dataList.get(0).keySet();
        result.put("columnNames", new ArrayList<>(columnNames));

        // 数据预览
        List<Map<String, Object>> previewData = dataList.stream()
                .limit(5)
                .collect(Collectors.toList());
        result.put("previewData", previewData);

        // 数据统计 (针对数值列)
        Map<String, Map<String, Object>> statistics = new HashMap<>();
        for (String column : columnNames) {
            // 尝试将列转换为数值进行统计
            List<BigDecimal> numericValues = new ArrayList<>();
            for (Map<String, Object> row : dataList) {
                Object value = row.get(column);
                if (value instanceof Number) {
                    numericValues.add(new BigDecimal(value.toString()));
                } else if (value instanceof String) {
                    try {
                        numericValues.add(new BigDecimal((String) value));
                    } catch (NumberFormatException e) {
                        // 非数值类型，忽略
                    }
                }
            }

            // 如果该列有数值数据，进行统计
            if (!numericValues.isEmpty()) {
                Map<String, Object> stats = calculateStatistics(numericValues);
                statistics.put(column, stats);
            }
        }

        result.put("statistics", statistics);
        return result;
    }

    private Map<String, Object> calculateStatistics(List<BigDecimal> values) {
        Map<String, Object> stats = new HashMap<>();

        if (values.isEmpty()) {
            return stats;
        }

        // 计算统计值
        BigDecimal sum = values.stream().reduce(BigDecimal.ZERO, BigDecimal::add);
        BigDecimal min = values.stream().min(BigDecimal::compareTo).orElse(BigDecimal.ZERO);
        BigDecimal max = values.stream().max(BigDecimal::compareTo).orElse(BigDecimal.ZERO);
        BigDecimal average = sum.divide(BigDecimal.valueOf(values.size()), 2, BigDecimal.ROUND_HALF_UP);

        // 计算标准差
        BigDecimal variance = values.stream()
                .map(v -> v.subtract(average).pow(2))
                .reduce(BigDecimal.ZERO, BigDecimal::add)
                .divide(BigDecimal.valueOf(values.size()), 2, BigDecimal.ROUND_HALF_UP);
        BigDecimal stdDev = sqrt(variance, 2);

        stats.put("count", values.size());
        stats.put("sum", sum);
        stats.put("min", min);
        stats.put("max", max);
        stats.put("average", average);
        stats.put("stdDev", stdDev);

        return stats;
    }

    // BigDecimal开平方方法
    private BigDecimal sqrt(BigDecimal value, int scale) {
        if (value.compareTo(BigDecimal.ZERO) < 0) {
            throw new IllegalArgumentException("平方根不能为负数");
        }

        // 使用牛顿迭代法计算平方根
        BigDecimal x = value.compareTo(BigDecimal.ZERO)==0 ? BigDecimal.valueOf(1) :value;
        while (true) {
            BigDecimal next = x.add(value.divide(x, scale, BigDecimal.ROUND_HALF_UP))
                    .divide(BigDecimal.valueOf(2), scale, BigDecimal.ROUND_HALF_UP);
            if (next.subtract(x).abs().compareTo(BigDecimal.valueOf(1e-10)) <= 0) {
                return next;
            }
            x = next;
        }
    }
}